﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using Model;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;

namespace DAL
{
    public class PhotoDAL : IPhotoDAL
    {

        private static readonly string SQL_CONN_SQLSERVER = ConfigurationManager.AppSettings["DBConn"];
        // Static constants
        private const string SQL_SELECT_PHOTOS = "SELECT PT_photoId, PT_albumId, PT_photoName,PT_generateTime,PT_impression,PT_commentCount,PT_photoRef FROM Photo";
        private const string SQL_SELECT_PHOTO = "SELECT PT_photoId, PT_albumId, PT_photoName,PT_generateTime,PT_impression,PT_commentCount,PT_photoRef FROM Photo WHERE PT_photoId = @photoId";
        private const string PARM_PHOTO_ID = "@photoId";
        //private const string SQL_CONN_SQLSERVER = "Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True";

        ///<summary>
        ///根据传入的ID判断照片是否存在
        ///</summary>
        public bool idExists(int phodoId)
        {
            //Set up a return value
            bool isExist = false;
            string sqlStatement = "SELECT PT_photoId FROM Photo WHERE PT_photoId = @photoId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", phodoId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }


        ///<summary>
        ///根据传入的照片名字判断照片是否存在
        ///</summary>
        public bool nameExists(string name)
        {
            //Set up a return value
            bool isExist = false;
            string sqlStatement = "SELECT PT_photoId FROM Photo WHERE PT_photoName = @photoName";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoName", name);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }


        ///<summary>
        ///根据传入的照片ID得到相册ID
        ///</summary>
        public int photoIdGetAlbum(int photoId)
        {
            //Set up a return value

            int album = 0;
            string sqlStatement = "SELECT PT_albumId FROM Photo WHERE PT_photoId= @photoId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", photoId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                    {
                        album = Convert.ToInt32(rdr.GetString(0));
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return album;

        }


        ///<summary>
        ///增加一张照片
        ///</summary>
        public bool Add(Model.Photo photo)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "INSERT INTO Photo VALUES(@albumId, @photoName,@generateTime,@impression,@commentCount,@photoRef )";
            Model.Photo photos = photo;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@albumId", photos.getPT_albumId()), new SqlParameter("@photoName", photos.getPT_photoName()), new SqlParameter("@generateTime", photos.getPT_generateTime()), new SqlParameter("@impression", photos.getPT_impression()), new SqlParameter("@commentCount", photos.getPT_commentCount()), new SqlParameter("@photoRef", photos.getPT_photoRef()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }



        ///<summary>
        ///更新一张照片
        ///</summary>
        public bool Update(Model.Photo photo)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "UPDATE Photo SET PT_albumId=@albumId, PT_photoName=@photoName,PT_generateTime=@time,PT_impression=@impression,PT_commentCount=@count,PT_photoRef=@photoRef  WHERE PT_photoId = @photoId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@albumId", photo.getPT_albumId()), new SqlParameter("@photoName", photo.getPT_photoName()), new SqlParameter("@time", photo.getPT_generateTime()), new SqlParameter("@impression", photo.getPT_impression()), new SqlParameter("@count", photo.getPT_commentCount()), new SqlParameter("@photoRef", photo.getPT_photoRef()), new SqlParameter("@photoId", photo.getPT_photoId()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }



        ///<summary>
        ///根据photoId删除一张照片
        ///</summary>
        public bool photoDeleteById(int photoId)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "DELETE FROM Photo WHERE PT_photoId = @photoId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", photoId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        ///<summary>
        ///根据照片名字删除一张照片
        ///</summary>
        public bool photoDeleteByName(string photoName)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "DELETE FROM Photo WHERE PT_photoName = @photoName";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoName", photoName);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }



        ///<summary>
        ///通过id查询照片是否存在，返回照片对象
        ///</summary>
        public Model.Photo getPhotoById(int photoId)
        {

            //Set up a return value
            Model.Photo photo = new Model.Photo();

            string sqlStatement = "SELECT PT_photoId, PT_albumId, PT_photoName,PT_generateTime,PT_impression,PT_commentCount,PT_photoRef FROM Photo WHERE PT_photoId = @photoId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", photoId);

                //Execute the query	
                SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para);

                while(rdr.Read())
                {
                        photo.setPT_photoId(Convert.ToInt32(rdr.GetInt32(0)));
                        photo.setPT_albumId(Convert.ToInt32(rdr.GetInt32(1)));
                        photo.setPT_photoName(Convert.ToString(rdr.GetString(2)));
                        photo.setPT_generateTime(Convert.ToDateTime(rdr.GetDateTime(3)));
                        photo.setPT_impression(Convert.ToString(rdr.GetString(4)));
                        photo.setPT_commentCount(Convert.ToInt32(rdr.GetInt32(5)));
                        photo.setPT_photoRef(Convert.ToString(rdr.GetString(6)));
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return photo;

        }


        ///<summary>
        ///通过name查询照片是否存在，返回照片对象
        ///</summary>
        public Model.Photo getPhotoByName(string photoName)
        {

            //Set up a return value
            Model.Photo photo = new Model.Photo();

            string sqlStatement = "SELECT PT_photoId, PT_albumId, PT_photoName,PT_generateTime,PT_impression,PT_commentCount,PT_photoRef FROM Photo WHERE PT_photoName = @photoName";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoName", photoName);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                        photo.setPT_photoId(Convert.ToInt32(rdr.GetInt32(0)));
                        photo.setPT_albumId(Convert.ToInt32(rdr.GetInt32(1)));
                        photo.setPT_photoName(Convert.ToString(rdr.GetString(2)));
                        photo.setPT_generateTime(Convert.ToDateTime(rdr.GetDateTime(3)));
                        photo.setPT_impression(Convert.ToString(rdr.GetString(4)));
                        photo.setPT_commentCount(Convert.ToInt32(rdr.GetInt32(5)));
                        photo.setPT_photoRef(Convert.ToString(rdr.GetString(6)));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return photo;

        }


        ///<summary>
        ///通过name查询照片是否存在，返回照片列表
        ///</summary>
        public List<Model.Photo> getListByName(string photoName) 
        {
            List<Model.Photo> photolist = new List<Model.Photo>();

            string sqlStatement = "SELECT PT_photoId, PT_albumId, PT_photoName,PT_generateTime,PT_impression,PT_commentCount,PT_photoRef FROM Photo WHERE PT_photoName LIKE @photoName";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                string PhotoName = "%" +  photoName  + "%";
                para = new SqlParameter("@photoName", PhotoName);

                //Execute the query	
                SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para);
                while (rdr.Read())
                {
                    Model.Photo photo = new Model.Photo();
                    photo.setPT_photoId(Convert.ToInt32(rdr.GetInt32(0)));
                    photo.setPT_albumId(Convert.ToInt32(rdr.GetInt32(1)));
                    photo.setPT_photoName(Convert.ToString(rdr.GetString(2)));
                    photo.setPT_generateTime(Convert.ToDateTime(rdr.GetDateTime(3)));
                    photo.setPT_impression(Convert.ToString(rdr.GetString(4)));
                    photo.setPT_commentCount(Convert.ToInt32(rdr.GetInt32(5)));
                    photo.setPT_photoRef(Convert.ToString(rdr.GetString(6)));
                    
                    photolist.Add(photo);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return photolist;
        }


        ///<summary>
        ///通过印象查询照片是否存在，返回照片列表
        ///</summary>
        public List<Model.Photo> getListByImp(string photoImp)
        {
            List<Model.Photo> photolist = new List<Model.Photo>();

            string sqlStatement = "SELECT PT_photoId, PT_albumId, PT_photoName,PT_generateTime,PT_impression,PT_commentCount,PT_photoRef FROM Photo WHERE PT_impression LIKE @photoImp";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                string PhotoImp = "%" + photoImp + "%";
                para = new SqlParameter("@photoImp", PhotoImp);

                //Execute the query	
                SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para);
                while (rdr.Read())
                {
                    Model.Photo photo = new Model.Photo();
                    photo.setPT_photoId(Convert.ToInt32(rdr.GetInt32(0)));
                    photo.setPT_albumId(Convert.ToInt32(rdr.GetInt32(1)));
                    photo.setPT_photoName(Convert.ToString(rdr.GetString(2)));
                    photo.setPT_generateTime(Convert.ToDateTime(rdr.GetDateTime(3)));
                    photo.setPT_impression(Convert.ToString(rdr.GetString(4)));
                    photo.setPT_commentCount(Convert.ToInt32(rdr.GetInt32(5)));
                    photo.setPT_photoRef(Convert.ToString(rdr.GetString(6)));

                    photolist.Add(photo);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return photolist;
        }


        public List<Model.Photo> getListByAlbum(int albumId) 
        {
            List<Model.Photo> photolist = new List<Model.Photo>();

            string sqlStatement = "SELECT PT_photoId, PT_albumId, PT_photoName,PT_generateTime,PT_impression,PT_commentCount,PT_photoRef FROM Photo WHERE PT_albumId = @albumId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@albumId", albumId);

                //Execute the query	
                SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para);
                while (rdr.Read())
                {
                    Model.Photo photo = new Model.Photo();
                    photo.setPT_photoId(Convert.ToInt32(rdr.GetInt32(0)));
                    photo.setPT_albumId(Convert.ToInt32(rdr.GetInt32(1)));
                    photo.setPT_photoName(Convert.ToString(rdr.GetString(2)));
                    photo.setPT_generateTime(Convert.ToDateTime(rdr.GetDateTime(3)));
                    photo.setPT_impression(Convert.ToString(rdr.GetString(4)));
                    photo.setPT_commentCount(Convert.ToInt32(rdr.GetInt32(5)));
                    photo.setPT_photoRef(Convert.ToString(rdr.GetString(6)));

                    photolist.Add(photo);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return photolist;
        }


        ///<summary>
        ///返回评论
        ///</summary>
        string getImpressionById(int photoId)
        {

            //Set up a return value

            string impression = null;
            string sqlStatement = "SELECT PT_impression FROM Photo WHERE PT_photoId= @photoId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", photoId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                    {
                        impression = Convert.ToString(rdr.GetString(0));
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return impression;

        }


        ///<summary>
        ///返回评论数
        ///</summary>
        public int getCommentsCountById(int photoId)
        {

            //Set up a return value

            int commentCount = 0;
            string sqlStatement = "SELECT PT_commentCount FROM Photo WHERE PT_photoId= @photoId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", photoId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                    {
                        commentCount = Convert.ToInt32(rdr.GetString(0));
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return commentCount;

        }


    }
}
